package org.alex.samples.util;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.text.DecimalFormat;
import java.text.ParseException;
import org.alex.samples.model.ExamUserVO;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {

	public static String OFFICE_EXCEL_2003_POSTFIX=".xls";
	public static String OFFICE_EXCEL_2007_POSTFIX=".xlsx";

	public static Workbook openWorkbook(InputStream is,String postfix){
		Workbook hssfWorkbook=null;
		try{
			if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
				hssfWorkbook=new HSSFWorkbook(is);
			}else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
				hssfWorkbook=new XSSFWorkbook(is);
			}
		}catch (Exception e) {
			if(e instanceof NotOLE2FileException){
				return null;
			}
			e.printStackTrace();
		}
		return hssfWorkbook;
	}

	public static List<ExamUserVO> getStudentListByExcel(Workbook wk) {
		final String GENDER_MALE_STRING="男",GENDER_FEMALE_STRING="女";
		final Integer GENDER_MALE_INTEGER=0,GENDER_FEMALE_INTEGER=1;
		List<ExamUserVO> resultList=new ArrayList<ExamUserVO>();
		Sheet sheet=wk.getSheetAt(0);
		int maxCellNumber=0;
		int maxRowNumber=sheet.getLastRowNum();
		ExamUserVO vo=null;
		for(int currentRowNumber=1;currentRowNumber<=maxRowNumber;currentRowNumber++){
			Row row=sheet.getRow(currentRowNumber);
			maxCellNumber=row.getLastCellNum();
			vo=new ExamUserVO();
			for(int currentCellNumber=0;currentCellNumber<maxCellNumber;currentCellNumber++){
				Cell cell=row.getCell(currentCellNumber);
				switch (currentCellNumber) {
				case 0:
					vo.setUserId(getValue(cell));
					break;
				case 1:
					vo.setUserName(getValue(cell));
					break;
				case 2:
					String gender=getValue(cell);
					if(GENDER_MALE_STRING.equals(gender)){
						vo.setGender(GENDER_MALE_INTEGER);
					}else if(GENDER_FEMALE_STRING.equals(gender)){
						vo.setGender(GENDER_FEMALE_INTEGER);
					}
					break;
				case 3:
					vo.setTel(getValue(cell));
					break;
				case 4:
					vo.setEmail(getValue(cell));
					break;
				case 5:
					vo.setAddress(getValue(cell));
					break;
				case 6:
					String dateString=getValue(cell);
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					Date date = null;
					try {
						date = sdf.parse(dateString);
					} catch (ParseException e) {
						e.printStackTrace();
					}
					vo.setBirthday(date);
					break;
				case 7:
					String decodingPassword=getValue(cell);
					vo.setPassword(MD5Utils.GetMD5Code(decodingPassword));
					break;
				}
			}
			resultList.add(vo);
		}
		return resultList;
	}
	
	@SuppressWarnings("static-access")
	private static String getValue(Cell cell){
		if(cell==null){
			return "";
		}
		if(cell.getCellType()==cell.CELL_TYPE_BOOLEAN){
			return String.valueOf(cell.getBooleanCellValue());
		}else if(cell.getCellType()==cell.CELL_TYPE_NUMERIC){
			DecimalFormat df=new DecimalFormat("0");
			return df.format(cell.getNumericCellValue());
		}else{
			return String.valueOf(cell.getStringCellValue());
		}
	}
	
}
